SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.[DnnProduct_CDS_CategoriesUpdate]
	@ID int,
	@Name nvarchar(50),
	@Description nvarchar(512),
	@ParentID int,
	@IsReadOnly bit = 0
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Path1 nvarchar(4000)
	DECLARE @Path2 nvarchar(4000)
	
	SELECT @Path1 = CASE
						WHEN len([Path]) = 0 THEN Name
						ELSE [Path] + '/' + Name
					END
	FROM [DnnProduct_CDS_Categories]
	WHERE [ID] = @ID
	
	IF (@ParentID > 0)
		SELECT @Path2 = CASE
							WHEN len([Path]) = 0 THEN Name
							ELSE [Path] + '/' + Name
						END
		FROM [DnnProduct_CDS_Categories]
		WHERE [ID] = @ParentID
	ELSE
		SET @Path2 = ''

	UPDATE [DnnProduct_CDS_Categories]
	SET
		Name = @Name,
		[Description] = @Description,
		ParentID = @ParentID,
		[Path] = @Path2,
		[IsReadOnly] = @IsReadOnly
	WHERE [ID] = @ID

	IF (len(@Path2) > 0)
		SET @Path2 = @Path2 + '/' + @Name
	ELSE
		SET @Path2 = @Name
	
	;WITH CTE (ID, ParentID)
	AS
	(
		SELECT ID, ParentID FROM [DnnProduct_CDS_Categories]
		WHERE ParentID = @ID
		UNION ALL
		SELECT c.ID, c.ParentID FROM [DnnProduct_CDS_Categories] c
		JOIN CTE ON CTE.ID = c.ParentID
	)
	
	UPDATE [DnnProduct_CDS_Categories]
	SET [Path] = CASE WHEN [Path] = @Path1 THEN @Path2 ELSE @Path2 + SUBSTRING([Path], LEN(@Path1) + 1, LEN([Path]) - LEN(@Path1)) END
	WHERE ID IN (SELECT ID FROM CTE)
END
GO
